﻿Imports System.IO
Imports System.Data.SqlClient
Namespace QuanLyKhachHang
    Public Class SQLDataHelper
        Private cmd As SqlCommand
        Private isOpen As Boolean
        Public Sub New()
            Dim conn As New SqlConnection(GetConnectionString())
            cmd = conn.CreateCommand()
        End Sub
        Public Sub ClearParameter()
            cmd.CreateParameter()
        End Sub
        Public Sub Open()
            If isOpen Then
                Return
            End If
            If cmd.Connection.State <> ConnectionState.Open Then
                cmd.Connection.Open()
                isOpen = True
            End If
        End Sub
        Public Sub Close()
            cmd.Connection.Close()
            isOpen = False
        End Sub
        Public Sub Dispose()
            cmd.Connection.Dispose()
            cmd.Dispose()
            Me.Close()
        End Sub
        ''' <summary>
        ''' Đọc chuỗi connectstring từ file PathConfig.txt
        ''' </summary>
        ''' <remarks>
        ''' </remarks>
        ''' <history>
        ''' </history>
        ''' <returns>
        ''' Chuỗi connect string tới SQL Server 2005
        ''' </returns>
        ''' -----------------------------------------------------------------------------
        Public Function GetConnectionString() As String
            Try
                Dim CurrentDir As String = Path.GetDirectoryName(Application.ExecutablePath)
                Dim PathConfigFile As String = CurrentDir + "\SQLConn.txt"
                Dim Content As String = System.IO.File.ReadAllText(PathConfigFile)
                Dim ConnectString As String
                Dim encrypt As New Common
                ConnectString = encrypt.Decrypt(Content)
                Return ConnectString
            Catch ex As Exception
                Throw ex
            End Try
        End Function
        Public Sub AddParameter(ByVal name As String, ByVal parameter As Object)
            Dim sqlpr As SqlParameter = cmd.CreateParameter
            sqlpr.ParameterName = name
            sqlpr.Value = parameter
            cmd.Parameters.Add(sqlpr)
        End Sub
        Public Function ExecuteReader(ByVal commandText As String, ByVal type As CommandType)
            Dim rd As SqlDataReader
            Try
                Me.Open()
                cmd.CommandText = commandText
                cmd.CommandType = type
                rd = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                Throw ex
            Finally
                cmd.Dispose()
            End Try
            Return rd
        End Function
        Public Function ExecuteDataTable(ByVal commandText As String, ByVal type As CommandType)
            Dim dt As New DataTable
            Try
                Me.Open()
                cmd.CommandText = commandText
                cmd.CommandType = type
                Dim adapter As New SqlDataAdapter
                adapter.SelectCommand = cmd
                adapter.Fill(dt)
            Catch ex As Exception
                Throw ex
            Finally
                cmd.Dispose()
                cmd.Connection.Close()
            End Try
            Return dt
        End Function
        Public Function ExecuteScalar(ByVal CommandText As String, ByVal Type As CommandType) As Object
            Dim row As Object = Nothing
            Try
                Me.Open()
                cmd.CommandText = CommandText
                cmd.CommandType = Type
                row = cmd.ExecuteScalar()
            Catch ex As Exception
                Throw ex
            Finally
                cmd.Dispose()
                Me.Close()
            End Try
            Return row
        End Function
       
    End Class
End Namespace
